---
title: "HR Movement dashboard"
output: 
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: fill
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(openxlsx)
library(tidyverse)
library(plotly)
library(ggmap)



#Reading regional files

americas=read.xlsx("https://people.bu.edu/kalathur/datasets/hr/Americas.xlsx", sheet = "data")

apac=read.xlsx("https://people.bu.edu/kalathur/datasets/hr/APAC.xlsx", sheet = "data")

europe=read.xlsx("https://people.bu.edu/kalathur/datasets/hr/Europe.xlsx",sheet = "data")


#Consolidating global data
global=rbind(americas,apac,europe)

#Read file containing country coordinates
country_coor=read.xlsx("https://people.bu.edu/kalathur/datasets/hr/country_coord.xlsx")

```

# Dash {data-icon="fa-globe"}

## Row {data-width="150"}

### Total new hires

```{r}
newhires=global %>% filter(`Type.of.movement`=="Entry") %>% nrow()
valueBox(value = newhires,icon = "fa-user-plus",caption = "New Hires",color = "green")
```

### Total exits

```{r}
exits=global %>% filter(`Type.of.movement`=="Exit") %>% nrow()
valueBox(value = exits,icon = "fa-user-times",caption = "Exits", color = "orange")
```

### Net change

```{r}
newhires=global %>% filter(`Type.of.movement`=="Entry") %>% nrow()
exits=global %>% filter(`Type.of.movement`=="Exit") %>% nrow()

netchange = (newhires-exits)

#If loop to have either up-arrow or down-arrow icon on valuebox based on the value of netchange
if(netchange>0){
  valueBox(value = netchange,icon = "fa-arrow-up",caption = "Net Change", color = "coral")
} else{
valueBox(value = netchange,icon = "fa-arrow-down",caption = "Net Change", color = "coral")}

```

## Row

### Movement by month

```{r}
h1=global %>% group_by(Month,`Type.of.movement`) %>% 
  summarise(count=n())

p1=plot_ly(data = h1,
        x=h1$Month,
        y=h1$count) %>% 
  add_lines(linetype = h1$`Type.of.movement`,
            data = h1$count,
            hoverinfo="text",
            text=paste(h1$count)) %>% 
  layout(xaxis=list(title="Month"),
         yaxis=list(title="Count")) 
p1

```

### Movement by Region

```{r}
#Count by month, entry/exit and country
h2=global %>% 
  group_by(Month,`Type.of.movement`,Country) %>% 
  summarise(count=n())

#Add long/lat info based on country name from countr_coor
h2=left_join(h2,country_coor[,2:4],by=c("Country"="name"))
#h2 is the table where I want the info
#country_coor[,2:4] selects only long, lat and name columns
# so that I can only add the long and lat cols to h2 after the join
#by=c() identifies the keys in both dataframes

p2=plot_geo(h2,locationmode="world") %>% 
  add_markers(x=h2$longitude,
              y=h2$latitude,
              size=h2$count,
              color=h2$`Type.of.movement`,
              hoverinfo="text",
              hovertext=paste(h2$`Type.of.movement`,": ",h2$count)) %>% 
  layout()


p2

```

# New hires {data-icon="fa-user-plus"}

## Row {data-height="250"}

### Avg New Hires YTD

```{r}
newhire_bymonth=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(Month) %>% 
  summarise(count=n())

avgnewhire=round(mean(newhire_bymonth$count),2)
  
valueBox(avgnewhire,icon = "fa-user-plus",caption = "Average monthly new hires",color = "green")
```

### New hire split by Employment

```{r}
h5=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(`Employment.type`) %>% 
  summarise(count=n())

p5=plot_ly(h5) %>% 
  add_pie(labels=h5$`Employment.type`,values=h5$count,hole=0.6)

p5
```

### New hire split by Work Authorisation

```{r}
h6=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(`Work.Authorisation`) %>% 
  summarise(count=n())

p6=plot_ly(h6) %>% 
  add_pie(labels=h6$`Work.Authorisation`,values=h6$count,hole=0.6)

p6

```

## Row

### New hires by Country

```{r}

#Summarise and group by Country
h3=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(Month,Country) %>% 
  summarise(count=n())

#Use spread to make the table ready for plots
h3=spread(h3,key = Country,value = count)


#Bar chart by country
p3=plot_ly(h3,
           x=h3$Month,
           hoverinfo="text") %>% 
  add_bars(y=h3$Argentina,
           name="Argentina",
           hovertext=paste(h3$Argentina)) %>% 
  add_bars(y=h3$Australia,
           name="Australia",
           hovertext=paste("Australia: ",h3$Australia)) %>% 
  add_bars(y=h3$Brazil,
           name="Brazil",
           hovertext=paste("Brazil: ",h3$Brazil)) %>%
  add_bars(y=h3$Canada,
           name="Canada",
           hovertext=paste("Canada: ",h3$Canada)) %>%
  add_bars(y=h3$India,
           name="India",
           hovertext=paste("India: ",h3$India)) %>%
  add_bars(y=h3$Romania,
           name="Romania",
           hovertext=paste("Romania: ",h3$Romania)) %>%
  add_bars(y=h3$USA,
           name="USA",
           hovertext=paste("USA",h3$USA))
  
  
p3
```

### New hires by Org

```{r}
#Create a summary grouped by Org
h4=global %>% 
  filter(`Type.of.movement`=="Entry") %>% 
  group_by(Month,Org) %>% 
  summarise(count=n())

#Make the table plot-ready
h4=spread(h4,key = Org,value = count)

p4=plot_ly(data = h4,
           x=h4$Month,
           hoverinfo="text") %>% 
  add_bars(y=h4$Corporate,
           name="Corporate",
           hovertext=paste("Corporate: ",h4$Corporate)) %>% 
  add_bars(y=h4$Delivery,
           name="Delivery",
           hovertext=paste("Delivery: ",h4$Delivery)) %>% 
  add_bars(y=h4$DeliveryOps,
           name="DeliveryOps",
           hovertext=paste("DeliveryOps: ",h4$DeliveryOps)) %>% 
  add_bars(y=h4$Support,
           name="Support",
           hovertext=paste("Support: ",h4$Support))
p4
```

# Attrition {data-icon="fa-user-times"}

## Row {data-height="250"}

### Avg Attrition YTD

```{r}
attn_bymonth=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(Month) %>% 
  summarise(count=n())

avgattn=round(mean(attn_bymonth$count),2)
  
valueBox(avgattn,icon = "fa-user-times",caption = "Average monthly attrition",color = "orange")
```

### Attrition split by Employment

```{r}
h7=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(`Employment.type`) %>% 
  summarise(count=n())

p7=plot_ly(h7) %>% 
  add_pie(labels=h7$`Employment.type`,values=h7$count,hole=0.6)

p7
```

### Attrition split by Work Authorisation

```{r}
h8=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(`Work.Authorisation`) %>% 
  summarise(count=n())

p8=plot_ly(h8) %>% 
  add_pie(labels=h8$`Work.Authorisation`,values=h8$count,hole=0.6)

p8

```

## Row

### Attrition by Country

```{r}

#Summarise and group by Country
h9=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(Month,Country) %>% 
  summarise(count=n())

#Use spread to make the table ready for plots
h9=spread(h9,key = Country,value = count)


#Bar chart by country
p9=plot_ly(h9,
           x=h9$Month,
           hoverinfo="text") %>% 
  add_bars(y=h9$Australia,
           name="Australia",
           hovertext=paste("Australia: ",h9$Australia)) %>% 
  add_bars(y=h9$India,
           name="India",
           hovertext=paste("India: ",h9$India)) %>%
  add_bars(y=h9$Romania,
           name="Romania",
           hovertext=paste("Romania: ",h9$Romania)) %>%
  add_bars(y=h9$USA,
           name="USA",
           hovertext=paste("USA",h9$USA))
  
  
p9
```

### Attrition by Org

```{r}
#Create a summary grouped by Org
h10=global %>% 
  filter(`Type.of.movement`=="Exit") %>% 
  group_by(Month,Org) %>% 
  summarise(count=n())

#Make the table plot-ready
h10=spread(h10,key = Org,value = count)

p10=plot_ly(data = h10,
           x=h10$Month,
           hoverinfo="text") %>% 
  add_bars(y=h10$Corporate,
           name="Corporate",
           hovertext=paste("Corporate: ",h10$Corporate)) %>% 
  add_bars(y=h10$Delivery,
           name="Delivery",
           hovertext=paste("Delivery: ",h10$Delivery)) %>% 
  add_bars(y=h10$DeliveryOps,
           name="DeliveryOps",
           hovertext=paste("DeliveryOps: ",h10$DeliveryOps)) %>% 
  add_bars(y=h10$Support,
           name="Support",
           hovertext=paste("Support: ",h10$Support))
p10
```